import pandas as pd
from sklearn.cluster import KMeans
import pickle
import networkx as nx
import matplotlib.pyplot as plt
import json

# 读取考生答题数据
df = pd.read_excel('exam.xlsx')  # 第一行为题目名，第一列为考生编号（如有）

# 计算总分
df['总分'] = df.sum(axis=1)

# K-means聚类
kmeans = KMeans(n_clusters=4, random_state=0)
df['分层'] = kmeans.fit_predict(df[['总分']])

# 统计每个分层的均值，排序后分配标签
layer_mean = df.groupby('分层')['总分'].mean().sort_values(ascending=False)
layer_label_map = {idx: label for idx, label in zip(layer_mean.index, ['优', '良好', '一般', '差'])}
df['分层标签'] = df['分层'].map(layer_label_map)

# 假设题目列名都在nodes列表中
nodes = [
    "选择1", "选择2", "选择3", "选择4", "选择5", "选择6", "选择7", "选择8", "选择9", "选择10",
    "填空1", "填空2", "填空3", "填空4", "填空5", "填空6",
    "计算1", "计算2", "证明1", "证明2", "综合应用1", "综合应用2"
]

node_label_map = {
    "选择1": "命题逻辑：蕴含式的成假赋值",
    "选择2": "谓词逻辑：自由变元与约束变元的区分",
    "选择3": "谓词逻辑：自由变元与约束变元的区分",
    "选择4": "关系性质：对称性判断",
    "选择5": "关系运算：复合关系",
    "选择6": "图论：度数序列可图性",
    "选择7": "图论：有向图的连通性分类",
    "选择8": "图论：生成子图的同构计数",
    "选择9": "树的性质：叶子节点计算",
    "选择10": "图论：欧拉图与哈密尔顿图的判别条件",
    "填空1": "命题逻辑：必要条件符号化",
    "填空2": "关系闭包：自反对称闭包的构造",
    "填空3": "图论：最小生成树权重",
    "填空4": "图论：握手定理应用",
    "填空5": "树的性质：顶点度数计算",
    "填空6": "图论：欧拉通路的识别",
    "计算1": "偏序集",
    "计算2": "图论",
    "证明1": "命题逻辑：自然推理系统的构造法",
    "证明2": "命题逻辑：归谬法（反证法）的应用",
    "综合应用1": "编码理论",
    "综合应用2": "图论"
}

grouped = df.groupby('分层')
group_stats = grouped[nodes].mean()  # 只对题目列做均值

# 定义掌握程度分级和颜色映射
mastery_level = [
    (0.0, 0.6, '极差'),
    (0.6, 0.7, '差'),
    (0.7, 0.8, '一般'),
    (0.8, 0.9, '良好'),
    (0.9, 1.01, '优秀')
]
color_map = {
    '极差': '#FF0000',   # 红色
    '差': '#FF4500',    # 橙红色
    '一般': '#FFD700',  # 金色
    '良好': '#00BFFF',  # 深天蓝
    '优秀': '#32CD32'   # 酸橙绿
}

# 设置中文字体
plt.rcParams['font.sans-serif'] = ['SimHei', 'Microsoft YaHei']
plt.rcParams['axes.unicode_minus'] = False

with open('knowledge_graph.gpickle', 'rb') as f:
    G = pickle.load(f)

# 只计算一次力导向布局
pos = nx.spring_layout(G, k=0.5, seed=40)  # seed保证每次运行都一样

# 新建总表用于存储所有分层的得分率和掌握程度
all_layers = []

for group, group_df in df.groupby('分层标签'):
    avg_scores = group_df[nodes].mean() / group_df[nodes].max()  # 得分率

    def get_level(score):
        if score < 0.6:
            return '极差'
        elif score < 0.7:
            return '差'
        elif score < 0.8:
            return '一般'
        elif score < 0.9:
            return '良好'
        else:
            return '优秀'

    # 构建DataFrame，包含知识点、描述、得分率、掌握程度、分层
    layer_df = pd.DataFrame({
        '知识点': nodes,
        '描述': [node_label_map[n] for n in nodes],
        f'{group}得分率': [round(avg_scores.get(n, 0), 3) for n in nodes],
        f'{group}掌握程度': [get_level(avg_scores.get(n, 0)) for n in nodes]
    })
    all_layers.append(layer_df)

# 合并所有分层的结果
result_df = all_layers[0][['知识点', '描述']].copy()
for layer_df in all_layers:
    result_df = result_df.merge(layer_df.drop(['描述'], axis=1), on='知识点')

# 计算每个分层的总分统计信息
boundary_stats = df.groupby('分层标签')['总分'].agg(['min', 'max', 'mean', 'std']).reset_index()
boundary_stats.columns = ['分层', '总分最小值', '总分最大值', '总分均值', '总分标准差']

# 输出到Excel，包含知识点掌握情况和分层边界
with pd.ExcelWriter('分层知识点掌握情况.xlsx', engine='openpyxl') as writer:
    result_df.to_excel(writer, sheet_name='知识点掌握情况', index=False)
    boundary_stats.to_excel(writer, sheet_name='分层边界', index=False)

# 以下为可视化部分（如需保留）
for group, group_df in df.groupby('分层标签'):
    avg_scores = group_df[nodes].mean() / group_df[nodes].max()  # 得分率
    node_levels = {n: get_level(avg_scores.get(n, 0)) for n in G.nodes}
    node_colors = [color_map.get(node_levels[n], '#808080') for n in G.nodes]
    plt.figure(figsize=(14, 9))
    nx.draw(
        G,
        with_labels=True,
        labels=node_label_map,
        node_color=node_colors,
        edge_color='gray',
        node_size=1200,
        font_size=10,
        pos=pos  # 关键：所有图用同一个pos
    )
    plt.title(f'【{group}】学生簇的知识点掌握情况结构图', fontsize=20, fontweight='bold', pad=20)
    plt.tight_layout(rect=[0, 0.03, 0.85, 0.95])  # 右侧留空间给图例
    plt.subplots_adjust(top=0.70, right=0.60)      # 右侧留空间给图例
    legend_elements = [
        plt.Line2D([0], [0], marker='o', color='w', markerfacecolor=color, markersize=20, label=label)
        for label, color in color_map.items()
    ]
    plt.legend(
        handles=legend_elements,
        loc='upper left',
        bbox_to_anchor=(0, 1),
        title='掌握程度',
        fontsize=15,
        title_fontsize=16,
        frameon=True
    )
    plt.figtext(
        0.5, 0.01,
        f'本图为【{group}】学生簇的知识点掌握情况结构图',
        ha='center', fontsize=12, color='gray'
    )
    plt.show()
